package com.btpn.custody.bean;

import java.io.IOException;
import java.io.PrintWriter;
import java.io.Serializable;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.HashSet;
import java.util.Set;

import javax.sql.DataSource;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Scope;
import org.springframework.dao.DataAccessException;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Component;

import com.btpn.custody.common.Constant;
import com.btpn.custody.common.LogEvent;
import com.btpn.custody.common.Query;
import com.btpn.custody.dao.IAccount;
import com.btpn.custody.dao.IAddress;
import com.btpn.custody.dao.ICIF;
import com.btpn.custody.dao.IDownloadLog;
import com.btpn.custody.dao.IMasterData;
import com.btpn.custody.entity.Account;
import com.btpn.custody.entity.Address;
import com.btpn.custody.entity.CIF;
import com.btpn.custody.entity.DownloadLog;
import com.btpn.custody.entity.MasterData;
import com.btpn.custody.service.IMasterService;

@Component("dailyTask")
@Scope("prototype")
public class DailyTask implements Runnable {

	private final org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(this.getClass());

	private StringWriter sw;
	private Thread thread;
	private Date dateFrom, dateTo;
//	private Date dailyDLdate;			// from DWH BUSS_DATE
	private Serializable idManual, idDaily;
	private String strUser;
	private Set<String> cifs = null;	// for address download triger

	@Autowired
	private IAccount accountDaoHibernate;

	@Autowired
	private IAddress addressDaoHibernate;

	@Autowired
	private ICIF cifDaoHibernate;

	@Autowired
	private DataSource CustodyDataSource;

	@Autowired
	private DataSource DamaroDataSource;

	@Autowired
	private DataSource DwhDataSource;

	@Autowired
	private IDownloadLog downloadLogDaoHibernate;

	@Autowired
	private IMasterData masterDataDaoHibernate;

	@Autowired
	private IMasterService masterDataService;

	@Autowired
	private ParameterBeans parameterBeans;

	@Value("${THREAD_DL}")int threadSum;

//	@PostConstruct
//	public void initDaily() {
//		System.out.println("============================> MAKE DailyTask");
//	}

	/*
	 * for daily daownload
	 * I make this method for invocation at scheduler (when use spring scheduler)
	 * Daily:type0
	 */
	public void dailyInitial(String user) {

		logger.info("<===== START DAILY DOWNLOAD =====>");
		Date fromDate = DateUtils.addDays(new Date(), -1);	// download H-1
		
		Date toDate= DateUtils.addSeconds(DateUtils.addDays(fromDate, 1), -1);
		logger.info("DAILY: " + fromDate +" To: "+toDate);
//		Date toDate = null;

		// get DWH buss_date
//		Connection dwhConn = null;
//		Statement dwhStmt = null;
//		ResultSet rs = null;
//		Date dailyDLdate = new Date();
		try {
//			dwhConn = DwhDataSource.getConnection();
//			dwhStmt = dwhConn.createStatement();
//			rs = dwhStmt.executeQuery(Query.GET_BUSSDATE);
//			if (rs.next()) dailyDLdate = MyDateUtils.strToDate(Constant.FORMAT_SQLSERVER, rs.getString("BUSS_DATE"));
//			logger.info("BUSS_DATE: " + dailyDLdate);

			idDaily = downloadLogDaoHibernate.create(new DownloadLog(fromDate, user, Constant.TYPE__DAILY + Constant.STRIP + Constant.STATUS__PROGRESS, null));

			if (copyAllView(user, Constant.TYPE__DAILY, fromDate, null)) {
				if (createCIF(user, Constant.TYPE__DAILY, fromDate)) {
					if (relateMDwithAddress(user, Constant.TYPE__DAILY, fromDate)) {
						if (relateMDwithAccount(user, Constant.TYPE__DAILY, fromDate)) {
//							relateMasterDataWithBranch(user);
							relateMasterDataWithBranch(user, fromDate, toDate);

							DownloadLog dl = downloadLogDaoHibernate.get((Long)idDaily);
							if (dl!=null) {
								dl.setDetail(Constant.TYPE__DAILY + Constant.STRIP + Constant.STATUS__SUCCESS);
								dl.setFinishedDate(new Date());
								downloadLogDaoHibernate.update(dl);
								logger.info("<===== SUCCESS DAILY DOWNLOAD =====> by {} at {}", user, new Date());
							}
						}
					}
				}
			}
		} catch (Exception e) {
			sw = new StringWriter();
			e.printStackTrace(new PrintWriter(sw));
			logger.error(sw.toString());

			DownloadLog dl = downloadLogDaoHibernate.get((Long)idDaily);
			dl.setDetail(Constant.TYPE__DAILY + Constant.STRIP + Constant.STATUS__FAIL);
			dl.setFinishedDate(new Date());
			dl.setException(sw.toString());
			downloadLogDaoHibernate.update(dl);
			logger.info("<===== FAIL DAILY DOWNLOAD =====> by {} at {}", user, new Date());
		} finally {
			System.gc();
			parameterBeans.setInProgress(false);

//			if (rs!=null) {
//				try {
//					rs.close();	rs=null;
//				} catch (SQLException e) {
//					logger.error(LogEvent.CONNECTION__CLOSE, e);
//				}
//			}
//			if (dwhStmt!=null) {
//				try {
//					dwhStmt.close(); dwhStmt=null;
//				} catch (SQLException e) {
//					logger.error(LogEvent.STATEMENT__CLOSE, e);
//				}
//			}
//			if (dwhConn != null) {
//				try {
//					dwhConn.close(); dwhConn=null;
//				} catch (SQLException e) {
//					logger.error(LogEvent.CONNECTION__CLOSE, e);
//				}
//			}
		}
	}

	// for MANUAL
	public void createThread(Date from, Date to) {
		idManual = downloadLogDaoHibernate.create(new DownloadLog(from, SecurityContextHolder.getContext().getAuthentication().getName(),
				Constant.TYPE__MANUAL + Constant.STRIP + Constant.STATUS__PROGRESS, null));
		dateFrom = from;
		dateTo = to;
		strUser = SecurityContextHolder.getContext().getAuthentication().getName();

		thread = new Thread(this);
		thread.start();
	}

	@Override
	public void run() {
		manualInitial(dateFrom, dateTo, strUser);
	}

	/*
	 * PURPOSE : for manual download
	 */
	public void manualInitial(Date from, Date to, String user) {

		logger.info("<===== START MANUAL DOWNLOAD =====>");
		logger.info("From:{} To:{}", from, to);

		try {


			if (copyAllView(user, Constant.TYPE__MANUAL, from, to)) {
				if (createCIF(user, Constant.TYPE__MANUAL, from)) {
					if (relateMDwithAddress(user, Constant.TYPE__MANUAL, from)) {
						if (relateMDwithAccount(user, Constant.TYPE__MANUAL, from)) {
							System.out.println("<====================> from: "+from +" To: "+to);
							relateMasterDataWithBranch(user, from, to);

							// update to success
							DownloadLog dl = downloadLogDaoHibernate.get((Long)idManual);
							if (dl!=null) {
								dl.setDetail(Constant.TYPE__MANUAL + Constant.STRIP + Constant.STATUS__SUCCESS);
								dl.setFinishedDate(new Date());
								downloadLogDaoHibernate.update(dl);
								logger.info("<===== SUCCESS MANUAL DOWNLOAD =====> by {} at {}", user, new Date());
							}
						}
					}
				}
			}


		} catch (Exception e) {
			sw = new StringWriter();
			e.printStackTrace(new PrintWriter(sw));
			logger.error(sw.toString());

			DownloadLog dl = downloadLogDaoHibernate.get((Long)idManual);
			dl.setDetail(Constant.TYPE__MANUAL + Constant.STRIP + Constant.STATUS__FAIL);
			dl.setFinishedDate(new Date());
			dl.setException(sw.toString());
			downloadLogDaoHibernate.update(dl);
			logger.info("<===== FAIL MANUAL DOWNLOAD =====> by {} at {}", user, new Date());
//			return false;
		} finally {
			System.gc();
			parameterBeans.setInProgress(false);
		}
	}

	/*
	 * PURPOSE : copy from DWH.view (VW_CIF, VW_ACCOUNT, VW_ADDRESS) to CMT TEMP where OPEN_DATE=BUSS_DATE
	 * 1. clean all in temp table
	 * 2. insert to CMT temp
	 */
	public Boolean copyAllView(String user, String type, Date from, Date to) {

		logger.info("<===== PART-A =====> COPY ALL VIEW : START");

		// convert date
//		java.sql.Timestamp fromTS = new Timestamp(from.getTime());
//		java.sql.Timestamp toTS = new Timestamp(to.getTime());

		// init
		cifs = new HashSet<String>();

		Connection dwhConn = null;
		Connection cmtConn = null;
		Connection damaroConn = null;
		Statement cmtStmt = null, dwhStmt = null;
		PreparedStatement ps_cmt = null, ps_cmt_address = null, ps_dwh_address = null, ps_damaro = null;
		ResultSet rs = null, rs_address = null, rs_damaro = null;
		int c_cif = 0, c_account = 0;
		try {
			dwhConn = DwhDataSource.getConnection();
			cmtConn = CustodyDataSource.getConnection();
			damaroConn = DamaroDataSource.getConnection();

			dwhStmt = dwhConn.createStatement();
			cmtStmt = cmtConn.createStatement();

			// must truncate before insert because these temporary table
			cmtStmt.executeUpdate("TRUNCATE TABLE " + Constant.CMT_CIF);
			cmtStmt.executeUpdate("TRUNCATE TABLE " + Constant.CMT_ACCOUNT);
			cmtStmt.executeUpdate("TRUNCATE TABLE " + Constant.CMT_ADDRESS);

/* ======================================================== CIF =================================================== */
			ps_cmt = cmtConn.prepareStatement("INSERT INTO " + Constant.CMT_CIF + "(" + Query.TEMPLATE__CIF + ")" +
					" VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
			ps_cmt_address = cmtConn.prepareStatement("INSERT INTO " + Constant.CMT_ADDRESS +
					"(CIF, ADDRESS_TYPE, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, ADDRESS5, POST_CODE)" +
					" VALUES (?,?,?,?,?,?,?,?)");

			logger.info("<== STEP-1 ==> COPY CIF : START");

			// daily
			if ((from!=null) && (to==null)) {
				rs = dwhStmt.executeQuery(Query.SELECT__VI_CIF + " WHERE " +
//						"OPEN_DATE = BUSS_DATE " +
						"OPEN_DATE = '" + Constant.formatSQLServer.format(from) + "' " +
						"ORDER BY CIF");
			} else {
			// manual
				rs = dwhStmt.executeQuery(Query.SELECT__VI_CIF + " WHERE " +
						"OPEN_DATE BETWEEN '" + Constant.formatSQLServer.format(from) + "' AND '" + Constant.formatSQLServer.format(to) + "' " +
						"ORDER BY CIF");
			}

			while (rs.next()) {
//				logger.info("Try to insert =====> {}", rs.getString("CIF"));
				ps_cmt.setString(1,rs.getString("AKTE_TERAKHIR_TANGGAL1"));
				ps_cmt.setString(2,rs.getString("AKTE_TERAKHIR_TANGGAL2"));
				ps_cmt.setString(3,rs.getString("ALAMAT_AHLI_WARIS1"));
				ps_cmt.setString(4,rs.getString("ALAMAT_AHLI_WARIS2"));
				ps_cmt.setString(5,rs.getString("CIF"));
				ps_cmt.setString(6,rs.getString("CUST_FULL_NAME"));
				ps_cmt.setString(7,rs.getString("ID_NUMBER"));
				ps_cmt.setString(8,rs.getString("INFO_AKTE_PENDIRIAN"));
				ps_cmt.setString(9,rs.getString("INFO_AKTE_PENDIRIAN2"));
				ps_cmt.setString(10,rs.getString("JABATAN"));
				ps_cmt.setString(11,rs.getString("JENIS_USAHA"));
				ps_cmt.setString(12,rs.getString("KOTA_AHLI_WARIS"));
				ps_cmt.setString(13,rs.getString("MOTHER_NAME"));
				ps_cmt.setString(14,rs.getString("NAMA_AHLI_WARIS"));
				ps_cmt.setString(15,rs.getString("NPWP"));
				ps_cmt.setString(16,rs.getString("PLACE_OF_BIRTH"));
				ps_cmt.setString(17,rs.getString("PROFESSION"));
				ps_cmt.setString(18,rs.getString("REK_BANK_LAIN1"));
				ps_cmt.setString(19,rs.getString("REK_BANK_LAIN2"));
				ps_cmt.setString(20,rs.getString("REK_BANK_LAIN3"));
				ps_cmt.setString(21,rs.getString("SIUP"));
				ps_cmt.setString(22,rs.getString("TANDA_DAFTAR_PERUSAHAAN"));
				ps_cmt.setString(23,rs.getString("HADDRESS1"));
				ps_cmt.setString(24,rs.getString("HADDRESS2"));
				ps_cmt.setString(25,rs.getString("HADDRESS3"));
				ps_cmt.setString(26,rs.getString("HADDRESS4"));
				ps_cmt.setString(27,rs.getString("HADDRESS5"));
				ps_cmt.setString(28,rs.getString("HPOST_CODE"));
				ps_cmt.setString(29,rs.getString("ADDITIONAL_INCOME"));
				ps_cmt.setString(30,rs.getString("BRANCH"));
				ps_cmt.setString(31,rs.getString("CURRENT_INCOME"));
				ps_cmt.setString(32,rs.getString("CUSTOMER_TYPE"));
				ps_cmt.setString(33,rs.getString("EDUCATION"));
				ps_cmt.setString(34,rs.getString("GENDER"));
				ps_cmt.setString(35,rs.getString("ID_TYPE"));
				ps_cmt.setString(36,rs.getString("HR_BUSSINESS"));
				ps_cmt.setString(37,rs.getString("HR_COUNTRIES"));
				ps_cmt.setString(38,rs.getString("HR_CUSTOMER"));
				ps_cmt.setString(39,rs.getString("HR_PRODUCT"));
				ps_cmt.setString(40,rs.getString("HUB_AHLI_WARIS"));
				ps_cmt.setString(41,rs.getString("HUB_DENGAN_BANK"));
				ps_cmt.setString(42,rs.getString("LAP_KEUANGAN"));
				ps_cmt.setString(43,rs.getString("LBU_GOL_NASABAH"));
				ps_cmt.setString(44,rs.getString("MARITAL_STATUS"));
				ps_cmt.setString(45,rs.getString("PARENT_COUNTRY"));
				ps_cmt.setString(46,rs.getString("PURPOSE"));
				ps_cmt.setString(47,rs.getString("RELIGION"));
				ps_cmt.setString(48,rs.getString("SOURCE_INCOME"));
				ps_cmt.setString(49,rs.getString("BIRTH_DATE"));
				ps_cmt.setString(50,rs.getString("ID_EXPIRED"));
				ps_cmt.setString(51,rs.getString("LAST_MAINTAINED_DATE"));
				ps_cmt.setString(52,rs.getString("OPEN_DATE"));
				ps_cmt.setString(53,rs.getString("TGL_AKHIR_TDP"));
				ps_cmt.setString(54,rs.getString("EST_TRX_NORMAL_CR"));
				ps_cmt.setString(55,rs.getString("EST_TRX_NORMAL_DB"));
				ps_cmt.setString(56,rs.getString("FREQ_TRX_CR"));
				ps_cmt.setString(57,rs.getString("FREQ_TRX_DB"));
				ps_cmt.setString(58,rs.getString("SALDO_NORMAL"));
				ps_cmt.executeUpdate();
				c_cif++;
				cifs.add(rs.getString("CIF"));

				logger.info("{}. Copied CIF[{}]", c_cif, rs.getString("CIF"));
			}
			logger.info("<== STEP-1 ==> COPY CIF : SUCCESS {} CIFs", c_cif);

/* ======================================================== ACCOUNT =================================================== */
			ps_cmt = cmtConn.prepareStatement("INSERT INTO " + Constant.CMT_ACCOUNT +
					"(BRANCH, CIF, ACCOUNT, SUFIX, ACCOUNT_TYPE, ACCOUNT_OPEN, REKENING_TYPE, CREATED_USER_ID, ACCOUNT_CLOSED)" +
					" VALUES (?,?,?,?,?,?,?,?,?)");

			// to make unique, filter by branch, cif, account, suffix
			ps_damaro = damaroConn.prepareStatement("SELECT USER_ID FROM " + Constant.DAMARO_VW_USER +
					" WHERE EXTERNAL_ACCOUNT_NUMBER=?" +
					" AND ID_JOB=(SELECT MAX(ID_JOB) FROM " + Constant.DAMARO_VW_USER + " WHERE EXTERNAL_ACCOUNT_NUMBER=?)");
//			" WHERE ACCOUNT_BRANCH=? AND BASIC_PART_OF_ACCOUNT_NUMBER=? AND EXTERNAL_ACCOUNT_NUMBER=? AND ACCOUNT_SUFFIX=?");

			// DL Account by ACCOUNT_OPEN
			logger.info("<== STEP-2 ==> COPY ACCOUNT : START");
			// daily
			if ((from!=null) & (to==null)) {
				rs = dwhStmt.executeQuery(Query.SELECT__VI_ACCOUNT + " WHERE LTRIM(RTRIM(ACCOUNT))<>'' AND " +
//						"ACCOUNT_OPEN = BUSS_DATE"
					"ACCOUNT_OPEN = '" + Constant.formatSQLServer.format(from) + "'"
				);
			// manual
			} else {
				rs = dwhStmt.executeQuery(Query.SELECT__VI_ACCOUNT + " WHERE LTRIM(RTRIM(ACCOUNT))<>'' AND " +
						"ACCOUNT_OPEN BETWEEN '" + Constant.formatSQLServer.format(from) + "' AND '" + Constant.formatSQLServer.format(to) + "'"
				);
			}
			while (rs.next()) {
				// get userId from damaro by account
				String user_create = "";
				String branchString = StringUtils.trimToEmpty(rs.getString("BRANCH"));
				String cifString = StringUtils.trimToEmpty(rs.getString("CIF"));
				String accString = StringUtils.trimToEmpty(rs.getString("ACCOUNT"));
				String sufixString = StringUtils.trimToEmpty(rs.getString("SUFIX"));

//				ps_damaro.setString(1, branchString);
//				ps_damaro.setString(2, cifString);
//				ps_damaro.setString(3, accString);
//				ps_damaro.setString(4, sufixString);
				ps_damaro.setString(1, accString);
				ps_damaro.setString(2, accString);
				rs_damaro = ps_damaro.executeQuery();

				if (rs_damaro.next()) {
					user_create = StringUtils.trimToEmpty(rs_damaro.getString("USER_ID"));	// userId from damaro
				}
				ps_cmt.setString(1, branchString);
				ps_cmt.setString(2, cifString);
				ps_cmt.setString(3, accString);
				ps_cmt.setString(4, sufixString);
				ps_cmt.setString(5, rs.getString("ACCOUNT_TYPE"));
				ps_cmt.setString(6, rs.getString("ACCOUNT_OPEN"));
				ps_cmt.setString(7, rs.getString("REKENING_TYPE"));
				ps_cmt.setString(8, user_create);
				ps_cmt.setString(9, rs.getString("ACCOUNT_CLOSED"));
				ps_cmt.executeUpdate();
				c_account++;
				cifs.add(cifString);
				logger.info("{}. Account[{}], UserId[{}]", new Object[] {c_account, accString, user_create});
			}
			logger.info("<== PART-2 ==> COPY ACCOUNT : SUCCESS {} accounts", c_account);

/* ======================================================== ADDRESS =================================================== */
			// after insert cif then sorting address in vi_address by cif
			c_cif=0;	// set to 0 just for counter in cifs
			ps_dwh_address = dwhConn.prepareStatement(Query.SELECT__VI_ADDRESS);
			for(String str : cifs) {
				int c_address = 0;
				ps_dwh_address.setString(1, str);
				rs_address = ps_dwh_address.executeQuery();
				while (rs_address.next()) {
					ps_cmt_address.setString(1, rs_address.getString("CIF"));
					ps_cmt_address.setString(2, rs_address.getString("ADDRESS_TYPE"));
					ps_cmt_address.setString(3, rs_address.getString("ADDRESS1"));
					ps_cmt_address.setString(4, rs_address.getString("ADDRESS2"));
					ps_cmt_address.setString(5, rs_address.getString("ADDRESS3"));
					ps_cmt_address.setString(6, rs_address.getString("ADDRESS4"));
					ps_cmt_address.setString(7, rs_address.getString("ADDRESS5"));
					ps_cmt_address.setString(8, rs_address.getString("POST_CODE"));
					ps_cmt_address.executeUpdate();
					c_address++;
				}
				c_cif++;
				logger.info("{}. CIF[{}] : {} addresses", new Object[] {c_cif, str, c_address});
			}

			logger.info("<===== PART-A =====> COPY ALL VIEW : FINISH by {}", new Object[] {Constant.USER_SYSTEM});

			// if no newCIF & no newAccount, return false
			if ((c_cif==0) & (c_account==0)) {
				if (type.equals(Constant.TYPE__MANUAL)) {
					DownloadLog dl = downloadLogDaoHibernate.get((Long)idManual);
					if (dl!=null) {
						dl.setDetail(Constant.TYPE__MANUAL + Constant.STRIP + Constant.STATUS__SUCCESS);
						dl.setFinishedDate(new Date());
						downloadLogDaoHibernate.update(dl);
						logger.info("<===== SUCCESS MANUAL DOWNLOAD =====> by {} at {}", user, new Date());
					}
				} else {
					DownloadLog dl = downloadLogDaoHibernate.get((Long)idDaily);
					if (dl!=null) {
						dl.setDetail(Constant.TYPE__DAILY + Constant.STRIP + Constant.STATUS__SUCCESS);
						dl.setFinishedDate(new Date());
						downloadLogDaoHibernate.update(dl);
						logger.info("<===== SUCCESS DAILY DOWNLOAD =====> by {} at {}", user, new Date());
					}
				}
				return false;
			}

			return true;
		} catch (Exception e) {
			logger.error(LogEvent.STEP_1, e);
			sw = new StringWriter();
			e.printStackTrace(new PrintWriter(sw));

			// update to fail
			if (type.equals(Constant.TYPE__MANUAL)) {
				DownloadLog dl = downloadLogDaoHibernate.get((Long)idManual);
				if (dl!=null) {
					dl.setDetail(Constant.TYPE__MANUAL + Constant.STRIP + Constant.STATUS__FAIL);
					dl.setFinishedDate(new Date());
					dl.setException( "Copy All View" + Constant.NEW_LINE + sw.toString());
					downloadLogDaoHibernate.update(dl);
				}
			} else {
				DownloadLog dl = downloadLogDaoHibernate.get((Long)idDaily);
				if (dl!=null) {
					dl.setDetail(Constant.TYPE__DAILY + Constant.STRIP + Constant.STATUS__FAIL);
					dl.setFinishedDate(new Date());
					dl.setException( "Copy All View" + Constant.NEW_LINE + sw.toString());
					downloadLogDaoHibernate.update(dl);
				}
			}
			return false;
		} finally {
			cifs.clear();cifs = null;

			if (sw!=null) {
				try {
					sw.close();sw=null;
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (rs!=null) {
				try {
					rs.close();	rs=null;
				} catch (SQLException e) {
					logger.error(LogEvent.CONNECTION__CLOSE, e);
				}
			}
			if (rs_address!=null) {
				try {
					rs_address.close();	rs_address=null;
				} catch (SQLException e) {
					logger.error(LogEvent.CONNECTION__CLOSE, e);
				}
			}
			if (rs_damaro!=null) {
				try {
					rs_damaro.close();	rs_damaro=null;
				} catch (SQLException e) {
					logger.error(LogEvent.CONNECTION__CLOSE, e);
				}
			}
			if (dwhStmt!=null) {
				try {
					dwhStmt.close(); dwhStmt=null;
				} catch (SQLException e) {
					logger.error(LogEvent.STATEMENT__CLOSE, e);
				}
			}
			if (cmtStmt!=null) {
				try {
					cmtStmt.close(); cmtStmt=null;
				} catch (SQLException e) {
					logger.error(LogEvent.STATEMENT__CLOSE, e);
				}
			}
			if (ps_cmt!=null) {
				try {
					ps_cmt.close();	ps_cmt=null;
				} catch (SQLException e) {
					logger.error(LogEvent.PREP_STMT__CLOSE, e);
				}
			}
			if (ps_cmt_address!=null) {
				try {
					ps_cmt_address.close();	ps_cmt_address=null;
				} catch (SQLException e) {
					logger.error(LogEvent.PREP_STMT__CLOSE, e);
				}
			}
			if (ps_dwh_address!=null) {
				try {
					ps_dwh_address.close();	ps_dwh_address=null;
				} catch (SQLException e) {
					logger.error(LogEvent.PREP_STMT__CLOSE, e);
				}
			}
			if (ps_damaro!=null) {
				try {
					ps_damaro.close(); ps_damaro=null;
				} catch (SQLException e) {
					logger.error(LogEvent.PREP_STMT__CLOSE, e);
				}
			}
			if (dwhConn != null) {
				try {
					dwhConn.close(); dwhConn=null;
				} catch (SQLException e) {
					logger.error(LogEvent.CONNECTION__CLOSE, e);
				}
			}
			if (cmtConn != null) {
				try {
					cmtConn.close(); cmtConn=null;
				} catch (SQLException e) {
					logger.error(LogEvent.CONNECTION__CLOSE, e);
				}
			}
		}
	}

	/*
	 * PURPOSE : createCIF, createMasterData, and addPrimeAddress for every TEMP_CIF
	 * NOTE : with cleaning exced space
	 * HOW TO : get by CIF, if nothing then create, if exist then do nothing
	 */
	public Boolean createCIF(String user, String type, Date from) {
		logger.info("<===== PART-B =====> CREATE CIF : START");

		// untuk mendata cif yg baru di buat
//		List<CIF> cifs = new ArrayList<CIF>();

		int c_cif=0, c_cif_created=0, c_cif_exsist=0;
		Connection cmtConn = null;
		Statement cmtStmt = null;
		ResultSet rs = null;
		try {
			cmtConn = CustodyDataSource.getConnection();
			cmtStmt = cmtConn.createStatement();

			String str_cif;
			rs = cmtStmt.executeQuery("SELECT * FROM " + Constant.CMT_CIF);
			while (rs.next()) {

				str_cif = StringUtils.deleteWhitespace(rs.getString("CIF"));

				// jika CIF belum ada di TBL_M_CIF maka create
				CIF tempCIF = cifDaoHibernate.getByCIF(str_cif);
				if (tempCIF==null) {
					CIF cif = new CIF();
					cif.setCreatedBy(user);
					cif.setLastUpdatedBy(user);

					cif.setCifSid(							str_cif);
					cif.setAKTE_TERAKHIR_TANGGAL1(			StringUtils.trimToEmpty(rs.getString("AKTE_TERAKHIR_TANGGAL1")));
					cif.setAKTE_TERAKHIR_TANGGAL2(			StringUtils.trimToEmpty(rs.getString("AKTE_TERAKHIR_TANGGAL2")));
					cif.setALAMAT_AHLI_WARIS1(				StringUtils.trimToEmpty(rs.getString("ALAMAT_AHLI_WARIS1")));
					cif.setALAMAT_AHLI_WARIS2(				StringUtils.trimToEmpty(rs.getString("ALAMAT_AHLI_WARIS2")));
					cif.setCUST_FULL_NAME(					StringUtils.trimToEmpty(rs.getString("CUST_FULL_NAME")));
					cif.setID_NUMBER(						StringUtils.deleteWhitespace(rs.getString("ID_NUMBER")));
					cif.setINFO_AKTE_PENDIRIAN(				StringUtils.trimToEmpty(rs.getString("INFO_AKTE_PENDIRIAN")));
					cif.setINFO_AKTE_PENDIRIAN2(			StringUtils.trimToEmpty(rs.getString("INFO_AKTE_PENDIRIAN2")));
					cif.setJABATAN(							StringUtils.trimToEmpty(rs.getString("JABATAN")));
					cif.setJENIS_USAHA(						StringUtils.trimToEmpty(rs.getString("JENIS_USAHA")));
					cif.setKOTA_AHLI_WARIS(					StringUtils.trimToEmpty(rs.getString("KOTA_AHLI_WARIS")));
					cif.setMOTHER_NAME(						StringUtils.trimToEmpty(rs.getString("MOTHER_NAME")));
					cif.setNAMA_AHLI_WARIS(					StringUtils.trimToEmpty(rs.getString("NAMA_AHLI_WARIS")));
					cif.setNPWP(							StringUtils.deleteWhitespace(rs.getString("NPWP")));
					cif.setPLACE_OF_BIRTH(					StringUtils.trimToEmpty(rs.getString("PLACE_OF_BIRTH")));
					cif.setPROFESSION(						StringUtils.trimToEmpty(rs.getString("PROFESSION")));
					cif.setREK_BANK_LAIN1(					StringUtils.deleteWhitespace(rs.getString("REK_BANK_LAIN1")));
					cif.setREK_BANK_LAIN2(					StringUtils.deleteWhitespace(rs.getString("REK_BANK_LAIN2")));
					cif.setREK_BANK_LAIN3(					StringUtils.deleteWhitespace(rs.getString("REK_BANK_LAIN3")));
					cif.setSIUP(							StringUtils.trimToEmpty(rs.getString("SIUP")));
					cif.setTANDA_DAFTAR_PERUSAHAAN(			StringUtils.trimToEmpty(rs.getString("TANDA_DAFTAR_PERUSAHAAN")));
					cif.setADDITIONAL_INCOME(				StringUtils.trimToEmpty(rs.getString("ADDITIONAL_INCOME")));
					cif.setBRANCH(							StringUtils.deleteWhitespace(rs.getString("BRANCH")));
					cif.setCURRENT_INCOME(					StringUtils.deleteWhitespace(rs.getString("CURRENT_INCOME")));
					cif.setCUSTOMER_TYPE(					StringUtils.deleteWhitespace(rs.getString("CUSTOMER_TYPE")));
					cif.setEDUCATION(						StringUtils.deleteWhitespace(rs.getString("EDUCATION")));
					cif.setGENDER(							StringUtils.deleteWhitespace(rs.getString("GENDER")));
					cif.setID_TYPE(							StringUtils.deleteWhitespace(rs.getString("ID_TYPE")));
					cif.setHR_BUSINESS(						StringUtils.deleteWhitespace(rs.getString("HR_BUSSINESS")));
					cif.setHR_COUNTRIES(					StringUtils.deleteWhitespace(rs.getString("HR_COUNTRIES")));
					cif.setHR_CUSTOMER(						StringUtils.deleteWhitespace(rs.getString("HR_CUSTOMER")));
					cif.setHR_PRODUCT(						StringUtils.deleteWhitespace(rs.getString("HR_PRODUCT")));
					cif.setHUB_AHLI_WARIS(					StringUtils.deleteWhitespace(rs.getString("HUB_AHLI_WARIS")));
					cif.setHUB_DENGAN_BANK(					StringUtils.deleteWhitespace(rs.getString("HUB_DENGAN_BANK")));
					cif.setLAP_KEUANGAN(					StringUtils.deleteWhitespace(rs.getString("LAP_KEUANGAN")));
					cif.setLBU_GOL_NASABAH(					StringUtils.deleteWhitespace(rs.getString("LBU_GOL_NASABAH")));
					cif.setMARITAL_STATUS(					StringUtils.deleteWhitespace(rs.getString("MARITAL_STATUS")));
					cif.setPARENT_COUNTRY(					StringUtils.deleteWhitespace(rs.getString("PARENT_COUNTRY")));
					cif.setPURPOSE(							StringUtils.deleteWhitespace(rs.getString("PURPOSE")));
					cif.setRELIGION(						StringUtils.deleteWhitespace(rs.getString("RELIGION")));
					cif.setSOURCE_INCOME(					StringUtils.deleteWhitespace(rs.getString("SOURCE_INCOME")));
					cif.setBIRTH_DATE(						rs.getTimestamp("BIRTH_DATE"));
					cif.setID_EXPIRED(						rs.getTimestamp("ID_EXPIRED"));
					cif.setLastUpdatedDate(					rs.getTimestamp("LAST_MAINTAINED_DATE"));
					cif.setOPEN_DATE(						rs.getTimestamp("OPEN_DATE"));
					cif.setTGL_AKHIR_TDP(					rs.getTimestamp("TGL_AKHIR_TDP"));
					cif.setEST_TRX_NORMAL_CR(				StringUtils.deleteWhitespace(rs.getString("EST_TRX_NORMAL_CR")));
					cif.setEST_TRX_NORMAL_DB(				StringUtils.deleteWhitespace(rs.getString("EST_TRX_NORMAL_DB")));
					cif.setFREQ_TRX_CR(						StringUtils.deleteWhitespace(rs.getString("FREQ_TRX_CR")));
					cif.setFREQ_TRX_DB(						StringUtils.deleteWhitespace(rs.getString("FREQ_TRX_DB")));
					cif.setSALDO_NORMAL(					StringUtils.deleteWhitespace(rs.getString("SALDO_NORMAL")));
					//cifDaoHibernate.create(cif);

					// createMasterData
					MasterData md = new MasterData();
					md.setCreatedBy(user);
					md.setUpdatedBy(user);
					md.setCIF(cif);

					// add prime address
					Address primeAddress = new Address();
					primeAddress.setCreatedBy(user);
					primeAddress.setLastUpdatedBy(user);
					primeAddress.setADDRESS1(		StringUtils.trimToEmpty(rs.getString("HADDRESS1")));
					primeAddress.setADDRESS2(		StringUtils.trimToEmpty(rs.getString("HADDRESS2")));
					primeAddress.setADDRESS3(		StringUtils.trimToEmpty(rs.getString("HADDRESS3")));
					primeAddress.setADDRESS4(		StringUtils.trimToEmpty(rs.getString("HADDRESS4")));
					primeAddress.setADDRESS5(		StringUtils.trimToEmpty(rs.getString("HADDRESS5")));
					primeAddress.setADDRESS_TYPE(	StringUtils.deleteWhitespace(Address.PRIME_ADDRESS));
					primeAddress.setPOST_CODE(		StringUtils.deleteWhitespace(rs.getString("HPOST_CODE")));

					primeAddress.setMasterData(md);
					md.getAddresses().add(primeAddress);
					masterDataDaoHibernate.create(md);

					logger.info("Created MD[{}]", str_cif);
					c_cif_created++;
				} else {
					// jika sudah ada maka do nothing by bussiness

					c_cif_exsist++;
				}

				c_cif++;
			}
			logger.info("<===== PART-B =====> CREATE CIF : SUCCESS From:{}, created:{}, exist:{}", new Object[] {c_cif, c_cif_created, c_cif_exsist});
//			return cifs;
			return true;
		} catch (SQLException e) {
			logger.error(LogEvent.STEP_2, e);
			sw = new StringWriter();
			e.printStackTrace(new PrintWriter(sw));

			// update to fail
			if (type.equals(Constant.TYPE__MANUAL)) {
				DownloadLog dl = downloadLogDaoHibernate.get((Long)idManual);
				if (dl!=null) {
					dl.setDetail(Constant.TYPE__MANUAL + Constant.STRIP + Constant.STATUS__FAIL);
					dl.setFinishedDate(new Date());
					dl.setException("Create CIF" + Constant.NEW_LINE + sw.toString());
					downloadLogDaoHibernate.update(dl);
				}
			} else {
				DownloadLog dl = downloadLogDaoHibernate.get((Long)idDaily);
				if (dl!=null) {
					dl.setDetail(Constant.TYPE__DAILY + Constant.STRIP + Constant.STATUS__FAIL);
					dl.setFinishedDate(new Date());
					dl.setException("Create CIF" + Constant.NEW_LINE + sw.toString());
					downloadLogDaoHibernate.update(dl);
				}
			}
			return false;
		} finally {
			if (sw!=null) {
				try {
					sw.close();sw=null;
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (rs!=null) {
				try {
					rs.close();	rs=null;
				} catch (SQLException e) {
					logger.error(LogEvent.CONNECTION__CLOSE, e);
				}
			}
			if (cmtStmt!=null) {
				try {
					cmtStmt.close();
				} catch (SQLException e) {
					logger.error(LogEvent.STATEMENT__CLOSE, e);
				}
			}
			if (cmtConn != null) {
				try {
					cmtConn.close();
				} catch (SQLException e) {
					logger.error(LogEvent.CONNECTION__CLOSE, e);
				}
			}
		}
	}

	/*
	 * PURPOSE : create Address for every TEMP_ADDRESS and relating with MasterData
	 * CASE: if md not found then search it from DWH
	 */
	public Boolean relateMDwithAddress(String user, String type, Date from) {
		logger.info("<===== PART-C =====> RELATE ADDRESS : START");

		int c_address=0, c_address_created=0, c_address_exsist=0, c_master_created=0;
		Connection cmtConn = null;
		Statement cmtStmt = null;
		ResultSet rs = null;
		try {
			cmtConn = CustodyDataSource.getConnection();
			cmtStmt = cmtConn.createStatement();

			String address_type;
			rs = cmtStmt.executeQuery("SELECT * FROM " + Constant.CMT_ADDRESS);
			while (rs.next()) {

				address_type = StringUtils.deleteWhitespace(rs.getString("ADDRESS_TYPE"));

				Address address = new Address();
				address.setCreatedBy(user);
				address.setLastUpdatedBy(user);
				address.setADDRESS1(		StringUtils.trimToEmpty(rs.getString("ADDRESS1")));
				address.setADDRESS2(		StringUtils.trimToEmpty(rs.getString("ADDRESS2")));
				address.setADDRESS3(		StringUtils.trimToEmpty(rs.getString("ADDRESS3")));
				address.setADDRESS4(		StringUtils.trimToEmpty(rs.getString("ADDRESS4")));
				address.setADDRESS5(		StringUtils.trimToEmpty(rs.getString("ADDRESS5")));
				address.setADDRESS_TYPE(	address_type);
				address.setPOST_CODE(		StringUtils.deleteWhitespace(rs.getString("POST_CODE")));

				String strCIF = StringUtils.deleteWhitespace(rs.getString("CIF"));

				// relate CIF-MasterData - Address
				MasterData md = masterDataDaoHibernate.getByCif(strCIF);
				if (md == null) {								// jika ada pembukaan cif baru/lama

					// cek ke DWH
					md = getMDfromDWH(strCIF, user);
					if (md!=null) {

						address.setMasterData(md);
						md.getAddresses().add(address);
						masterDataDaoHibernate.create(md);

						logger.info("Created AddressType[{}] Created MD[{}] related", address_type, strCIF);
						c_address_created++;
						c_master_created++;
					}
					// else CIF ga ketemu di DWH
				} else {										// masterData not null (CIF exsist)

					// get address by cif and addressType
					Address a = addressDaoHibernate.getByMD_AddressType(md.getSid(), address.getADDRESS_TYPE());

					if (a==null) {
						address.setMasterData(md);
						addressDaoHibernate.create(address);

						logger.info("Created AddressType[{}] related with {}", address_type, strCIF);
						c_address_created++;
					} else {									// if address exist then NOT update
						c_address_exsist++;
//						a.setADDRESS1(address.getADDRESS1());
//						a.setADDRESS2(address.getADDRESS2());
//						a.setADDRESS3(address.getADDRESS3());
//						a.setADDRESS4(address.getADDRESS4());
//						a.setADDRESS5(address.getADDRESS5());
//						a.setPOST_CODE(address.getPOST_CODE());
//						a.setLastUpdatedBy(user);
//						a.setLastUpdatedDate(new Date());
//						addressDaoHibernate.update(a);
					}
				}
				c_address++;
			}
			logger.info("<===== PART-C =====> RELATE ADDRESS : SUCCESS From:{}, created:{}, exist:{}, MD created:{}", new Object[] {c_address, c_address_created, c_address_exsist, c_master_created});
			return true;
		} catch (SQLException e) {
			logger.error(LogEvent.STEP_3, e);
			sw = new StringWriter();
			e.printStackTrace(new PrintWriter(sw));

			// update to fail
			if (type.equals(Constant.TYPE__MANUAL)) {
				DownloadLog dl = downloadLogDaoHibernate.get((Long)idManual);
				if (dl!=null) {
					dl.setDetail(Constant.TYPE__MANUAL + Constant.STRIP + Constant.STATUS__FAIL);
					dl.setFinishedDate(new Date());
					dl.setException("Relate MD with Address" + Constant.NEW_LINE + sw.toString());
					downloadLogDaoHibernate.update(dl);
				}
			} else {
				DownloadLog dl = downloadLogDaoHibernate.get((Long)idDaily);
				if (dl!=null) {
					dl.setDetail(Constant.TYPE__DAILY + Constant.STRIP + Constant.STATUS__FAIL);
					dl.setFinishedDate(new Date());
					dl.setException("Relate MD with Address" + Constant.NEW_LINE + sw.toString());
					downloadLogDaoHibernate.update(dl);
				}
			}
			return false;
		} finally {
			if (sw!=null) {
				try {
					sw.close();sw=null;
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (rs!=null) {
				try {
					rs.close();	rs=null;
				} catch (SQLException e) {
					logger.error(LogEvent.CONNECTION__CLOSE, e);
				}
			}
			if (cmtStmt!=null) {
				try {
					cmtStmt.close();
				} catch (SQLException e) {
					logger.error(LogEvent.STATEMENT__CLOSE, e);
				}
			}
			if (cmtConn != null) {
				try {
					cmtConn.close();
				} catch (SQLException e) {
					logger.error(LogEvent.CONNECTION__CLOSE, e);
				}
			}
		}
	}

	/*
	 * PURPOSE : create Account for every TEMP_ACCOUNT and relating with CIF
	 * CASE: if md not found then search it from DWH
	 */
	public Boolean relateMDwithAccount(String user, String type, Date from) {
		logger.info("<===== PART-D =====> RELATE ACCOUNT : START");

		int c_account=0, c_account_created=0, c_account_exsist=0, c_master_created=0, c_account_duplicate=0;
		Connection cmtConn = null;
		Statement cmtStmt = null;
		ResultSet rs = null;
		try {
			cmtConn = CustodyDataSource.getConnection();
			cmtStmt = cmtConn.createStatement();

			rs = cmtStmt.executeQuery("SELECT * FROM " + Constant.CMT_ACCOUNT);
			while (rs.next()) {

				String str_cif = StringUtils.deleteWhitespace(rs.getString("CIF"));
				String str_branch = StringUtils.deleteWhitespace(rs.getString("BRANCH"));
				String str_account = StringUtils.deleteWhitespace(rs.getString("ACCOUNT"));
				String str_sufix = StringUtils.deleteWhitespace(rs.getString("SUFIX"));
				try {
					Account account = accountDaoHibernate.getByCifAccountSufix(str_cif, str_account, str_sufix);
					if (account==null) {
						account = new Account();
						account.setACCOUNT(str_account);
						account.setBRANCH(str_branch);
						account.setSUFIX(str_sufix);
						account.setACCOUNT_OPEN(	rs.getTimestamp("ACCOUNT_OPEN"));
						account.setACCOUNT_CLOSE(	rs.getTimestamp("ACCOUNT_CLOSED"));
						account.setACCOUNT_TYPE(	StringUtils.deleteWhitespace(rs.getString("ACCOUNT_TYPE")));
						account.setREKENING_TYPE(	StringUtils.deleteWhitespace(rs.getString("REKENING_TYPE")));
						account.setCREATED_USER_ID(	StringUtils.deleteWhitespace(rs.getString("CREATED_USER_ID")));
						account.setCreatedBy(user);
						account.setLastUpdatedBy(user);

						// relate CIF - Account
						MasterData md = masterDataDaoHibernate.getByCif(str_cif);
						if (md == null) {

							// cek ke DWH
							md = getMDfromDWH(str_cif, user);
							if (md!=null) {

								account.setMasterData(md);
								md.getAccounts().add(account);

								masterDataDaoHibernate.create(md);
//								if(md.getCIF().getCifSid().endsWith("0000AF")) System.out.println("=============================");
								logger.info("Created Account[{}] Created MD[{}] related", str_account, str_cif);
								c_account_created++;
								c_master_created++;
							}
							// else CIF	not found in DWH
						} else {
							// relate account with MD
							account.setMasterData(md);
							accountDaoHibernate.create(account);

							logger.info("Created Account[{}] related with {}", str_account, str_cif);
							c_account_created++;
						}
					} else {
						c_account_exsist++;
					}
				} catch (DataAccessException dae) {
					// This account have been exsist for different CIF
					logger.error("Created Account Fail:{} for CIF[{}] details:{}", new Object[] { str_account, str_cif, dae });
					c_account_duplicate++;
				}
				c_account++;
			}
			logger.info("<===== PART-D =====> RELATE ACCOUNT : SUCCESS From:{}, created:{}, exist:{}, duplicate:{}, MD created:{}", new Object[] {c_account, c_account_created, c_account_exsist, c_account_duplicate, c_master_created});
			return true;
		} catch (SQLException e) {
			logger.error(LogEvent.STEP_4, e);
			sw = new StringWriter();
			e.printStackTrace(new PrintWriter(sw));

			// update to fail
			if (type.equals(Constant.TYPE__MANUAL)) {
				DownloadLog dl = downloadLogDaoHibernate.get((Long)idManual);
				if (dl!=null) {
					dl.setDetail(Constant.TYPE__MANUAL + Constant.STRIP + Constant.STATUS__FAIL);
					dl.setFinishedDate(new Date());
					dl.setException("Relate MD with Account" + Constant.NEW_LINE + sw.toString());
					downloadLogDaoHibernate.update(dl);
				}
			} else {
				DownloadLog dl = downloadLogDaoHibernate.get((Long)idDaily);
				if (dl!=null) {
					dl.setDetail(Constant.TYPE__DAILY + Constant.STRIP + Constant.STATUS__FAIL);
					dl.setFinishedDate(new Date());
					dl.setException("Relate MD with Account" + Constant.NEW_LINE + sw.toString());
					downloadLogDaoHibernate.update(dl);
				}
			}
			return false;
		} finally {
			if (sw!=null) {
				try {
					sw.close();sw=null;
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (rs!=null) {
				try {
					rs.close();	rs=null;
				} catch (SQLException e) {
					logger.error(LogEvent.CONNECTION__CLOSE, e);
				}
			}
			if (cmtStmt!=null) {
				try {
					cmtStmt.close();
				} catch (SQLException e) {
					logger.error(LogEvent.STATEMENT__CLOSE, e);
				}
			}
			if (cmtConn != null) {
				try {
					cmtConn.close();
				} catch (SQLException e) {
					logger.error(LogEvent.CONNECTION__CLOSE, e);
				}
			}
		}
	}

	/*
	 * PURPOSE : get CIF from DWH by CIFid
	 */
	public MasterData getMDfromDWH(String cifNumber, String user) {

		MasterData md = null;

		Connection dwhConn = null;
		Statement dwhStmt = null;
		ResultSet rs = null;
		try {
			dwhConn = DwhDataSource.getConnection();
			dwhStmt = dwhConn.createStatement();

			rs = dwhStmt.executeQuery("SELECT TOP 1 * FROM " + Constant.DWH_VW_CIF + " WHERE CIF='" + cifNumber + "'");
			if (rs.next()) {
				CIF cif = new CIF();
				cif.setCreatedBy(user);
				cif.setLastUpdatedBy(user);
				cif.setCifSid(cifNumber);
				cif.setAKTE_TERAKHIR_TANGGAL1(			StringUtils.trimToEmpty(rs.getString("AKTE_TERAKHIR_TANGGAL1")));
				cif.setAKTE_TERAKHIR_TANGGAL2(			StringUtils.trimToEmpty(rs.getString("AKTE_TERAKHIR_TANGGAL2")));
				cif.setALAMAT_AHLI_WARIS1(				StringUtils.trimToEmpty(rs.getString("ALAMAT_AHLI_WARIS1")));
				cif.setALAMAT_AHLI_WARIS2(				StringUtils.trimToEmpty(rs.getString("ALAMAT_AHLI_WARIS2")));
				cif.setCUST_FULL_NAME(					StringUtils.trimToEmpty(rs.getString("CUST_FULL_NAME")));
				cif.setID_NUMBER(						StringUtils.deleteWhitespace(rs.getString("ID_NUMBER")));
				cif.setINFO_AKTE_PENDIRIAN(				StringUtils.trimToEmpty(rs.getString("INFO_AKTE_PENDIRIAN")));
				cif.setINFO_AKTE_PENDIRIAN2(			StringUtils.trimToEmpty(rs.getString("INFO_AKTE_PENDIRIAN2")));
				cif.setJABATAN(							StringUtils.trimToEmpty(rs.getString("JABATAN")));
				cif.setJENIS_USAHA(						StringUtils.trimToEmpty(rs.getString("JENIS_USAHA")));
				cif.setKOTA_AHLI_WARIS(					StringUtils.trimToEmpty(rs.getString("KOTA_AHLI_WARIS")));
				cif.setMOTHER_NAME(						StringUtils.trimToEmpty(rs.getString("MOTHER_NAME")));
				cif.setNAMA_AHLI_WARIS(					StringUtils.trimToEmpty(rs.getString("NAMA_AHLI_WARIS")));
				cif.setNPWP(							StringUtils.deleteWhitespace(rs.getString("NPWP")));
				cif.setPLACE_OF_BIRTH(					StringUtils.trimToEmpty(rs.getString("PLACE_OF_BIRTH")));
				cif.setPROFESSION(						StringUtils.trimToEmpty(rs.getString("PROFESSION")));
				cif.setREK_BANK_LAIN1(					StringUtils.deleteWhitespace(rs.getString("REK_BANK_LAIN1")));
				cif.setREK_BANK_LAIN2(					StringUtils.deleteWhitespace(rs.getString("REK_BANK_LAIN2")));
				cif.setREK_BANK_LAIN3(					StringUtils.deleteWhitespace(rs.getString("REK_BANK_LAIN3")));
				cif.setSIUP(							StringUtils.trimToEmpty(rs.getString("SIUP")));
				cif.setTANDA_DAFTAR_PERUSAHAAN(			StringUtils.trimToEmpty(rs.getString("TANDA_DAFTAR_PERUSAHAAN")));
				cif.setADDITIONAL_INCOME(				StringUtils.trimToEmpty(rs.getString("ADDITIONAL_INCOME")));
				cif.setBRANCH(							StringUtils.deleteWhitespace(rs.getString("BRANCH")));
				cif.setCURRENT_INCOME(					StringUtils.deleteWhitespace(rs.getString("CURRENT_INCOME")));
				cif.setCUSTOMER_TYPE(					StringUtils.deleteWhitespace(rs.getString("CUSTOMER_TYPE")));
				cif.setEDUCATION(						StringUtils.deleteWhitespace(rs.getString("EDUCATION")));
				cif.setGENDER(							StringUtils.deleteWhitespace(rs.getString("GENDER")));
				cif.setID_TYPE(							StringUtils.deleteWhitespace(rs.getString("ID_TYPE")));
				cif.setHR_BUSINESS(						StringUtils.deleteWhitespace(rs.getString("HR_BUSSINESS")));
				cif.setHR_COUNTRIES(					StringUtils.deleteWhitespace(rs.getString("HR_COUNTRIES")));
				cif.setHR_CUSTOMER(						StringUtils.deleteWhitespace(rs.getString("HR_CUSTOMER")));
				cif.setHR_PRODUCT(						StringUtils.deleteWhitespace(rs.getString("HR_PRODUCT")));
				cif.setHUB_AHLI_WARIS(					StringUtils.deleteWhitespace(rs.getString("HUB_AHLI_WARIS")));
				cif.setHUB_DENGAN_BANK(					StringUtils.deleteWhitespace(rs.getString("HUB_DENGAN_BANK")));
				cif.setLAP_KEUANGAN(					StringUtils.deleteWhitespace(rs.getString("LAP_KEUANGAN")));
				cif.setLBU_GOL_NASABAH(					StringUtils.deleteWhitespace(rs.getString("LBU_GOL_NASABAH")));
				cif.setMARITAL_STATUS(					StringUtils.deleteWhitespace(rs.getString("MARITAL_STATUS")));
				cif.setPARENT_COUNTRY(					StringUtils.deleteWhitespace(rs.getString("PARENT_COUNTRY")));
				cif.setPURPOSE(							StringUtils.deleteWhitespace(rs.getString("PURPOSE")));
				cif.setRELIGION(						StringUtils.deleteWhitespace(rs.getString("RELIGION")));
				cif.setSOURCE_INCOME(					StringUtils.deleteWhitespace(rs.getString("SOURCE_INCOME")));
				cif.setBIRTH_DATE(						rs.getTimestamp("BIRTH_DATE"));
				cif.setID_EXPIRED(						rs.getTimestamp("ID_EXPIRED"));
				cif.setLastUpdatedDate(					rs.getTimestamp("LAST_MAINTAINED_DATE"));
				cif.setOPEN_DATE(						rs.getTimestamp("OPEN_DATE"));
				cif.setTGL_AKHIR_TDP(					rs.getTimestamp("TGL_AKHIR_TDP"));
				cif.setEST_TRX_NORMAL_CR(				StringUtils.deleteWhitespace(rs.getString("EST_TRX_NORMAL_CR")));
				cif.setEST_TRX_NORMAL_DB(				StringUtils.deleteWhitespace(rs.getString("EST_TRX_NORMAL_DB")));
				cif.setFREQ_TRX_CR(						StringUtils.deleteWhitespace(rs.getString("FREQ_TRX_CR")));
				cif.setFREQ_TRX_DB(						StringUtils.deleteWhitespace(rs.getString("FREQ_TRX_DB")));
				cif.setSALDO_NORMAL(					StringUtils.deleteWhitespace(rs.getString("SALDO_NORMAL")));

				md = new MasterData();
				md.setCreatedBy(user);
				md.setUpdatedBy(user);
				md.setCIF(cif);

				// add prime address
				Address primeAddress = new Address();
				primeAddress.setCreatedBy(user);
				primeAddress.setLastUpdatedBy(user);
				primeAddress.setADDRESS1(		StringUtils.trimToEmpty(rs.getString("HADDRESS1")));
				primeAddress.setADDRESS2(		StringUtils.trimToEmpty(rs.getString("HADDRESS2")));
				primeAddress.setADDRESS3(		StringUtils.trimToEmpty(rs.getString("HADDRESS3")));
				primeAddress.setADDRESS4(		StringUtils.trimToEmpty(rs.getString("HADDRESS4")));
				primeAddress.setADDRESS5(		StringUtils.trimToEmpty(rs.getString("HADDRESS5")));
				primeAddress.setADDRESS_TYPE(	StringUtils.deleteWhitespace(Address.PRIME_ADDRESS));
				primeAddress.setPOST_CODE(		StringUtils.deleteWhitespace(rs.getString("HPOST_CODE")));
				primeAddress.setMasterData(md);
				md.getAddresses().add(primeAddress);
			}
		} catch (SQLException e) {
			logger.error(LogEvent.GENERAL, e);
			return null;
		} finally {
			if (rs!=null) {
				try {
					rs.close();	rs=null;
				} catch (SQLException e) {
					logger.error(LogEvent.CONNECTION__CLOSE, e);
				}
			}
			if (dwhStmt!=null) {
				try {
					dwhStmt.close();
				} catch (SQLException e) {
					logger.error(LogEvent.STATEMENT__CLOSE, e);
				}
			}
			if (dwhConn != null) {
				try {
					dwhConn.close();
				} catch (SQLException e) {
					logger.error(LogEvent.CONNECTION__CLOSE, e);
				}
			}
		}
		return md;
	}

	/*
	 * PURPOSE : create MasterData for every CIF
	 */
	public void relateMasterDataWithBranch(String user) {
		logger.info("<===== PART-E =====> RELATE MasterData with Branch : START");

		String result = masterDataService.relateWithBranch(user);
		logger.info(result);

		logger.info("<===== PART-E =====> RELATE MasterData with Branch : SUCCESS");
	}
	
	
	public void relateMasterDataWithBranch(String user, Date from, Date to) {
		logger.info("<===== PART-E =====> RELATE MasterData with Branch : START");

		String result = masterDataService.relateWithBranch(user, from, to);
		logger.info(result);

		logger.info("<===== PART-E =====> RELATE MasterData with Branch : SUCCESS");
	}
	
}